<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Emailreport_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function getEmails(){
    	return $this->db->query('SELECT au.user_id,au.email FROM app_users au
								LEFT JOIN app_email_reports aer ON aer.`user_id` = au.`user_id`
								WHERE aer.user_id IS NULL')->result();
    }

    public function getEmail($e){
        return $this->db->query('SELECT aerd.email_dtl_id,
                                   au.`email`,
                                   au.user_id,
                                   aerd.email_id,
                                   CONCAT(au.`first_name`," ",au.`last_name`) NAME
                            FROM app_email_report_dtl aerd
                            LEFT JOIN app_email_reports aer ON aer.email_id = aerd.email_id
                            LEFT JOIN app_users au ON au.`user_id` = aer.`user_id`
                            WHERE aerd.email_id = "'.$e.'" LIMIT 1')->result();
    }

    public function getReports(){
    	return $this->db->query('SELECT * FROM app_lookup_values WHERE lookup_type = "REPORTS" AND enable_flag ="Y"')->result();
    }

    public function getSchedule(){
    	return $this->db->query('SELECT * FROM app_lookup_values WHERE lookup_type = "SCHDULE" AND enable_flag ="Y"')->result();
    }

    public function insert($data){
	    $this->db->query("call app_email_reports_ins(?,?,?,@err,@msg,@id)", $data);
        $que = $this->db->query("SELECT @err as err,@msg as msg,@id as id");
        $msg; $err; $code;
        foreach ($que->result() as $keys) {
            $err = $keys->err;
            $msg = $keys->msg;
            $code = $keys->id;
        }
        $arr = array(
            'err' => $err,
            'msg' => $msg,
            'id' => $code,
        );
        echo json_encode($arr);
    }

    public function insertReport($data){
        $q;
        $this->db->query("call app_email_reports_dtl_ins(?,?,?,?,?,?,?,?,@err,@msg)", $data);
        $que = $this->db->query("SELECT @err as err,@msg as msg");
        $msg; $err;
        foreach ($que->result() as $keys) {
            $err = $keys->err;
            $msg = $keys->msg;
        }
        $arr = array(
            'err' => $err,
            'msg' => $msg
        );
        return $arr;
    }

    public function getReportDtl($id){
        $q = $this->db->query('SELECT aerd.filetype,aerd.email_dtl_id ,aerd.email_id, aerd.`form_id`,aerd.lookup_code, aerd.times, alv.`meaning`,alv1.`meaning` form_name, aerd.`enable_flag`, aerd.`last_sent`, aerd.time, aerd.ampm FROM app_email_report_dtl aerd
                                LEFT JOIN app_lookup_values alv1 ON alv1.lookup_code = aerd.form_id AND alv1.`lookup_type` = "REPORTS"
                                LEFT JOIN app_lookup_values alv ON alv.`lookup_code` = aerd.lookup_code
                                WHERE aerd.`email_id` ='.$id);
            echo json_encode(array('err' => 0, 'file' => $q->result()));
    }

    public function getSchedules(){
        return $this->db->query('SELECT TIMESTAMPDIFF(HOUR, last_sent, NOW()) AS today,
                                       aerd.email_dtl_id,
                                       aerd.times,
                                       aerd.filetype,
                                       aerd.`form_id`,
                                       aerd.`lookup_code`,
                                       aerd.`enable_flag`,
                                       aerd.`last_sent`,
                                       au.`email`,
                                       aerd.email_id,
                                       alv1.`meaning` AS report_name,
                                       alv1.`description` AS parameter,
                                       CONCAT(au.`first_name`," ",au.`last_name`) name,
                                       alv.`description`,
                                       alv.`meaning` TIME
                                FROM app_email_report_dtl aerd
                                LEFT JOIN app_email_reports aer ON aer.email_id = aerd.email_id
                                LEFT JOIN app_users au ON au.`user_id` = aer.`user_id`
                                LEFT JOIN app_lookup_values alv1 ON alv1.lookup_code = aerd.form_id AND alv1.`lookup_type` = "REPORTS"
                                LEFT JOIN app_lookup_values alv ON alv.`lookup_code` = aerd.lookup_code
                                WHERE aerd.enable_flag = "Y"')->result();
    }

    public function emailLogs($data){
         $this->db->query("call app_email_que_ins(?,?,?,?,?)", $data);
    }

    public function getStatus(){
        return $this->db->query("SELECT aeq.`que_id`,
                                   aeq.`status`,
                                   aeq.`attachment`,
                                   CONCAT(au.`first_name`,' ',au.`last_name`) name,
                                   au.`email`,
                                   aeq.`created_date` last_try,
                                   alv1.`meaning` AS report_name,
                                   alv1.`description` AS parameter,
                                   alv.`description`,
                                   alv.`meaning` TIME FROM app_email_que aeq
                            LEFT JOIN app_email_report_dtl aerd ON aerd.`email_dtl_id` = aeq.`email_dtl_id`
                            LEFT JOIN app_email_reports aer ON aer.`email_id` = aerd.`email_id`
                            LEFT JOIN app_users au ON au.`user_id` = aer.`user_id`
                            LEFT JOIN app_lookup_values alv1 ON alv1.lookup_code = aerd.form_id AND alv1.`lookup_type` = 'REPORTS'
                            LEFT JOIN app_lookup_values alv ON alv.`lookup_code` = aerd.lookup_code")->result();
    }

    public function search($name,$email){
        $this->db->query('DROP TABLE IF EXISTS email_tmp');
        $this->db->query('CREATE TEMPORARY TABLE IF NOT EXISTS email_tmp AS (SELECT au.`email`,
                            aer.email_id,
                            CONCAT(au.`first_name`," ",au.`last_name`) name
                            FROM app_email_reports aer
                            LEFT JOIN app_email_report_dtl aerd ON aer.email_id = aerd.email_id
                            LEFT JOIN app_users au ON au.`user_id` = aer.`user_id`
                            GROUP BY aer.`email_id`)');
        $sql;
        if($name != "" && $email != ""){
            $sql = "SELECT * FROM email_tmp WHERE name LIKE ".$this->db->escape($name)." AND entity_name LIKE '".$email."' ";
        }else if($name == "" && $email != ""){
            $sql = "SELECT * FROM email_tmp WHERE email LIKE ".$this->db->escape($email)." ";
        }else{
            $sql = "SELECT * FROM email_tmp WHERE name LIKE ".$this->db->escape($name)." ";
        }
        
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function updateSave($data){
        $this->db->query("call app_email_reports_dtl_upd(?,?,?,?,?,?,@err,@msg)", $data);
        $que = $this->db->query("SELECT @err as err,@msg as msg");
        $msg; $err;
        foreach ($que->result() as $keys) {
            $err = $keys->err;
            $msg = $keys->msg;
        }
        $arr = array(
            'err' => $err,
            'msg' => $msg
        );
        return $arr;
    }

    public function getDataToDelete(){
        return $this->db->query('SELECT *
                                FROM app_email_que aeq
                                WHERE TIMESTAMPDIFF(DAY, created_date, NOW()) >= 7')->result();
    }

}